
using Youz.Common.DBUtility;
using Youz.CreditRepay.Entity;
using System;
using System.Data;
using System.Data.SqlClient;
using System.Text;
using System.Collections.Generic;

namespace Youz.CreditRepay.DAL
{
    /// <summary>
    /// Member_Account Data Class
    /// </summary>
    public partial class Member_AccountDAL : BaseDAL
    {
        private DbHelperSQL DbHelperSQL = new DbHelperSQL();
        public string TableName = "Member_Account";

        public Member_AccountDAL()
        { }

        #region  成员方法
        /// <summary>
        ///  新增一条数据
        /// </summary>
        public bool Add(Member_Account model)
        {
            StringBuilder strSql = new StringBuilder();

            strSql.Append("insert into [Member_Account](");
            strSql.Append("[MemberAccount],[Balance],[CashDeposit],[CreateTime],[ModifyTime]");
            strSql.Append(") values (");
            strSql.Append("@MemberAccount,@Balance,@CashDeposit,@CreateTime,@ModifyTime");
            strSql.Append(")");

            SqlParameter[] parameters = {

                    new SqlParameter("@MemberAccount", model.MemberAccount),
                    new SqlParameter("@Balance", model.Balance),
                    new SqlParameter("@CashDeposit", model.CashDeposit),
                    new SqlParameter("@CreateTime", model.CreateTime),
                    new SqlParameter("@ModifyTime", model.ModifyTime)
            };

            int rows = DbHelperSQL.ExecuteSql(strSql.ToString(), parameters);
            if (rows > 0)
            {
                return true;
            }
            else
            {
                return false;
            }
        }

        /// <summary>
        ///  保存一条数据
        /// </summary>
        public bool Update(Member_Account model)
        {
            StringBuilder strSql = new StringBuilder();

            strSql.Append("update [Member_Account] set ");
            strSql.Append("[Balance]=@Balance,[CashDeposit]=@CashDeposit,[CreateTime]=@CreateTime,[ModifyTime]=@ModifyTime");
            strSql.Append(" where [MemberAccount]=@MemberAccount");

            SqlParameter[] parameters = {

                    new SqlParameter("@MemberAccount", model.MemberAccount),
                    new SqlParameter("@Balance", model.Balance),
                    new SqlParameter("@CashDeposit", model.CashDeposit),
                    new SqlParameter("@CreateTime", model.CreateTime),
                    new SqlParameter("@ModifyTime", model.ModifyTime)
            };

            int rows = DbHelperSQL.ExecuteSql(strSql.ToString(), parameters);
            if (rows > 0)
            {
                return true;
            }
            else
            {
                return false;
            }
        }

        /// <summary>
        /// 删除一条数据
        ///</summary>
        public bool Delete(String MemberAccount)
        {
            StringBuilder strSql = new StringBuilder();
            strSql.Append("delete from [Member_Account] ");
            strSql.Append(" where [MemberAccount]=@MemberAccount");

            SqlParameter[] parameters = {

                new SqlParameter("@MemberAccount", MemberAccount)

            };

            int rows = DbHelperSQL.ExecuteSql(strSql.ToString(), parameters);
            if (rows > 0)
            {
                return true;
            }
            else
            {
                return false;
            }
        }

        /// <summary>
        /// 得到一个对象实体
        /// </summary>
        public Member_Account Get(String MemberAccount)
        {
            StringBuilder strSql = new StringBuilder();

            strSql.Append("select  top 1 * from [Member_Account] ");
            strSql.Append(" where [MemberAccount]=@MemberAccount");

            SqlParameter[] parameters = {

                new SqlParameter("MemberAccount", MemberAccount)

            };

            DataSet ds = DbHelperSQL.Query(strSql.ToString(), parameters);
            if (ds.Tables[0].Rows.Count > 0)
            {
                return Get(ds.Tables[0].Rows[0]);
            }
            else
            {
                return null;
            }
        }

        /// <summary>
        /// 得到一个对象实体
        /// </summary>
        public Member_Account Get(DataRow row)
        {
            if (row == null) return null;

            Member_Account model = new Member_Account();


            if (row["MemberAccount"] != DBNull.Value)
            {
                model.MemberAccount = (String)row["MemberAccount"];
            }

            if (row["Balance"] != DBNull.Value)
            {
                model.Balance = (Decimal)row["Balance"];
            }

            if (row["CashDeposit"] != DBNull.Value)
            {
                model.CashDeposit = (Decimal)row["CashDeposit"];
            }

            if (row["CreateTime"] != DBNull.Value)
            {
                model.CreateTime = (DateTime)row["CreateTime"];
            }

            if (row["ModifyTime"] != DBNull.Value)
            {
                model.ModifyTime = (DateTime)row["ModifyTime"];
            }


            return model;
        }

        /// <summary>
        /// 获得数据列表
        /// </summary>
        public DataSet Gets(string sqlWhere)
        {
            StringBuilder strSql = new StringBuilder();

            strSql.Append("select * from [Member_Account] ");
            if (sqlWhere.Trim() != "")
            {
                strSql.Append(" where " + sqlWhere);
            }

            return DbHelperSQL.Query(strSql.ToString());
        }

        /// <summary>
        /// 获得前几行数据
        /// </summary>
        public DataSet Gets(int Top, string sqlWhere, string filedOrder)
        {
            StringBuilder strSql = new StringBuilder();
            strSql.Append("select ");
            if (Top > 0)
            {
                strSql.Append(" top " + Top.ToString());
            }
            strSql.Append(" *  FROM [Member_Account] ");

            if (sqlWhere.Trim() != "")
            {
                strSql.Append(" where " + sqlWhere);
            }
            if (filedOrder.Trim() != "")
            {
                strSql.Append(" order by " + filedOrder);
            }

            return DbHelperSQL.Query(strSql.ToString());
        }

        /// <summary>
        /// 获取记录总数
        /// </summary>
        public int GetRecordCount(string sqlWhere)
        {
            StringBuilder strSql = new StringBuilder();
            strSql.Append("select count(1) FROM [Member_Account] ");
            if (sqlWhere.Trim() != "")
            {
                strSql.Append(" where " + sqlWhere);
            }
            object obj = DbHelperSQL.GetSingle(strSql.ToString());
            if (obj == null)
            {
                return 0;
            }
            else
            {
                return Convert.ToInt32(obj);
            }
        }

        /// <summary>
        /// 获得数据列表分页
        /// </summary>
        public DataTable Gets(string sqlWhere, int pageindex, int pagesize, out int total, params SqlParameter[] cmdParms)
        {
            StringBuilder strSql = new StringBuilder();

            strSql.Append(" SELECT * FROM [Member_Account]  ");
            if (sqlWhere.Trim() != "")
            {
                strSql.Append(" where " + sqlWhere);
            }

            int count = 0;
            DataTable dt = DbHelperSQL.GetPager(pageindex, pagesize, "" + strSql + "", "order by MemberAccount desc ", out count, cmdParms);
            total = count;
            return dt;
        }
        #endregion  成员方法

        /// <summary>
        /// 余额支付
        /// </summary>
        /// <param name="memberAccount"></param>
        /// <param name="amount"></param>
        /// <param name="fee"></param>
        /// <param name="payType"></param>
        /// <param name="tradeNo"></param>
        /// <returns></returns>
        public bool BalancePay(string memberAccount, decimal amount, decimal fee, string tradeNo)
        {
            List<SqlAndParams> list = new List<SqlAndParams>();
            
            //更新支付状态
            list.Add(new SqlAndParams { Sql = "update [Pay_Records] set [Status]=@Status, ModifyTime=@ModifyTime where TradeNo=@TradeNo",
                SqlParameters = new SqlParameter[] { new SqlParameter("@TradeNo", tradeNo), new SqlParameter("@Status", (int)PayRecordsStatus.Payed), new SqlParameter("@ModifyTime", DateTime.Now) } });

            #region 状态变更表
            StringBuilder strSql2 = new StringBuilder();
            strSql2.Append("insert into [Sys_StatusRecord](");
            strSql2.Append("[RecordId],[MemberAccount],[RecordTable],[RecordType],[BeforeValue],[AfterValue],[CreateTime],[Creator],[Memo],[ObjectID],[LoginID]");
            strSql2.Append(") values (");
            strSql2.Append("@RecordId,@MemberAccount,@RecordTable,@RecordType,@BeforeValue,@AfterValue,@CreateTime,@Creator,@Memo,@ObjectID,@LoginID");
            strSql2.Append(")");
            SqlParameter[] parameters2 = {
                    new SqlParameter("@RecordId", Guid.NewGuid()),
                    new SqlParameter("@MemberAccount", memberAccount),
                    new SqlParameter("@RecordTable", "Pay_Records"),
                    new SqlParameter("@RecordType", "Status"),
                    new SqlParameter("@BeforeValue", ""),
                    new SqlParameter("@AfterValue", (int)PayRecordsStatus.Payed),
                    new SqlParameter("@CreateTime", DateTime.Now),
                    new SqlParameter("@Creator", memberAccount),
                    new SqlParameter("@Memo", "还款计划支付保证金"),
                    new SqlParameter("@ObjectID", tradeNo),
                    new SqlParameter("@LoginID", null) };
            list.Add(new SqlAndParams() { Sql = strSql2.ToString(), SqlParameters = parameters2 });
            #endregion

            //保证金
            var listHistory = new List<AssetHistoryItem>();
            listHistory.Add(new AssetHistoryItem(AssetOperateType.Minus, AssetHistoryType.Balance, amount + fee));
            listHistory.Add(new AssetHistoryItem(AssetOperateType.Plus, AssetHistoryType.CashDeposit, amount));
            list.Add(new SqlAndParams { Sql = new Member_AssetHistoryDAL().GetSqlByOperateAsset(memberAccount, memberAccount, tradeNo, AssetHistoryChangeType.Repay, listHistory) });
            
            return DbHelperSQL.ExecuteSqlTran(list);
        }
    }
}


